package pwd.allen.export.easyexcel.writeHandler;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.expression.Expression;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.StandardEvaluationContext;

import java.util.*;

/**
 * 通过模板的批注实现动态列
 *
 * @author panweidan
 * @createDate 2020/7/16-10:53
 */
public class DynamiColWriteHandler implements SheetWriteHandler {

	private StandardEvaluationContext evaluationContext = new StandardEvaluationContext();

	public DynamiColWriteHandler(Map<String, Object> paramMap){
		evaluationContext.setRootObject(paramMap);
	}

	@Override
	public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
	}

	@Override
	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		List<Integer> delCol = getDelCol(writeSheetHolder.getCachedSheet());
		delCol(writeSheetHolder.getCachedSheet(), delCol);
	}

	/**
	 * 删除指定列
	 * @param sheet
	 * @param delList
	 */
	public static void delCol(Sheet sheet, List<Integer> delList) {
		// 排序
		Collections.sort(delList, new Comparator<Integer>() {
			@Override
			public int compare(Integer o1, Integer o2) {
				return o1 - o2;
			}
		});

		Map<CellRangeAddress, Object[]> mergedRegionMap = new HashMap<>();
		List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
		for (int i = mergedRegions.size() - 1; i >= 0; i--) {
			CellRangeAddress mergedRegion = mergedRegions.get(i);
			// 如果合并的列都删除，则该合并规则可以删除
			int colNum = 0;
			for (int j = mergedRegion.getFirstColumn(); j <= mergedRegion.getLastColumn(); j++) {
				if (!delList.contains(j)) {
					colNum++;
				}
			}
			if (colNum > 0) {
				Cell mergedCell = sheet.getRow(mergedRegion.getFirstRow()).getCell(mergedRegion.getFirstColumn());
				mergedRegionMap.put(mergedRegion, new Object[]{mergedCell.getStringCellValue(), mergedCell.getCellStyle()});
				int delNumBefore = 0;
				for (Integer integer : delList) {
					if (integer >= mergedRegion.getFirstColumn()) {
						break;
					}
					delNumBefore++;
				}
				mergedRegion.setFirstColumn(mergedRegion.getFirstColumn() - delNumBefore);
				mergedRegion.setLastColumn(mergedRegion.getFirstColumn() + colNum - 1);
			}
			sheet.removeMergedRegion(i);
		}

		//<editor-fold desc="复制单元格">
		Iterator<Row> rowIterator = sheet.rowIterator();
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			Iterator<Cell> cellIterator = row.cellIterator();
			int lastCol = 0;
			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				if (!delList.contains(cell.getColumnIndex())) {
					int delNumBefore = 0;
					for (Integer integer : delList) {
						if (integer >= cell.getColumnIndex()) {
							break;
						}
						delNumBefore++;
					}
					if (delNumBefore == 0) {
						lastCol = cell.getColumnIndex();
						continue;
					}
					lastCol = cell.getColumnIndex() - delNumBefore;
					Cell newCell = row.getCell(lastCol);
					if (newCell == null) {
						newCell = row.createCell(lastCol);
					}
					newCell.setCellStyle(cell.getCellStyle());
					newCell.setCellValue(cell.getStringCellValue());
					if (row.getRowNum() == 0) {
						sheet.setColumnWidth(newCell.getColumnIndex(), sheet.getColumnWidth(cell.getColumnIndex()));
					}
				}
			}
			lastCol++;
			while (lastCol <= row.getLastCellNum()) {
				Cell rmCell = row.getCell(lastCol);
				if (rmCell != null) {
					row.removeCell(rmCell);
				}
				if (row.getRowNum() == 0) {
					//设置为默认宽度
					sheet.setColumnWidth(lastCol, 2300);
				}
				lastCol++;
			}
		}
		//</editor-fold>

		//<editor-fold desc="设置合并">
		Iterator<Map.Entry<CellRangeAddress, Object[]>> entryIterator = mergedRegionMap.entrySet().iterator();
		while (entryIterator.hasNext()) {
			Map.Entry<CellRangeAddress, Object[]> entry = entryIterator.next();
			CellRangeAddress mergedRegion = entry.getKey();
			Row row = sheet.getRow(mergedRegion.getFirstRow());
			Cell newCell = row.getCell(mergedRegion.getFirstColumn());
			Object[] entryValue = entry.getValue();
			if (newCell == null) {
				newCell = row.createCell(mergedRegion.getFirstColumn());
				newCell.setCellStyle((CellStyle)entryValue[1]);
			}
			newCell.setCellValue((String)entryValue[0]);
			if (mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() > 0 || mergedRegion.getLastRow() - mergedRegion.getFirstRow() > 0) {
				sheet.addMergedRegion(mergedRegion);
			}
		}
		//</editor-fold>

	}


	public boolean option(String expr) {
		SpelExpressionParser parser = new SpelExpressionParser();
		Expression expression = parser.parseExpression(expr);
		return expression.getValue(evaluationContext, Boolean.class);
	}

	public List<Integer> getDelCol(Sheet sheet) {
		ArrayList<Integer> list = new ArrayList<>();
		Map<CellAddress, ? extends Comment> cellComments = sheet.getCellComments();
		Collection<? extends Comment> values = cellComments.values();
		for (Comment value : values) {
			String expression = value.getString().getString();
			expression = expression != null && expression.contains("\n") ? expression.split("\n")[1] : expression;
			if (StringUtils.isEmpty(expression)) {
				continue;
			}
			if (!option(expression)) {
				list.add(value.getAddress().getColumn());
			}
			// 删除批注
			sheet.getRow(value.getRow()).getCell(value.getColumn()).removeCellComment();
		}
		return list;
	}
}
